/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package utils;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import model.Encuesta;
import model.PreguntaRespuesta;

/**
 *
 * @author David
 */
public class ConectorBaseDatos {

        String host = "jdbc:mysql://54.235.109.171:3306/";
        String base_datos = "foraneos";
        String usuario = "david";
        String clave = "david123456";
        String tabla = "surveydata_minimercados";
        String id = "ID_SURVEY";
        String driver = "com.mysql.jdbc.Driver";
        List<String> ignorarColumnas;
        
        public ConectorBaseDatos() {
            ignorarColumnas = new ArrayList<>();
            ignorarColumnas.add("OU05_CODMANZ");
        }
        
        public List<String> conectar() {
            List<String> ids = null;
            Connection conn = null;
            try { 
                Class.forName(driver).newInstance(); 
                conn = DriverManager.getConnection(host+base_datos,usuario,clave); 
                Statement st = conn.createStatement(); 
                ResultSet res = st.executeQuery("SELECT "+ id +" FROM " + tabla); 
                if(res != null) {
                    ids = new ArrayList<>();
                    while (res.next()) { 
                        String surveyId = res.getString(id);
                        ids.add(surveyId);
                    }
                }
                
            } catch (Exception e) {
                e.printStackTrace(); 
            }finally {
                if(conn != null) {
                    try { 
                        conn.close();
                    } catch (SQLException ex) {
                        Logger.getLogger(ConectorBaseDatos.class.getName()).log(Level.SEVERE, null, ex);
                    }
                }
                return ids;
            }
        }
    
        public void crear(Encuesta encuesta){
            Connection conn = null;
            try {
                
                Class.forName(driver).newInstance(); 
                conn = DriverManager.getConnection(host+base_datos,usuario,clave); 
                Statement st = conn.createStatement(); 
                conn.setAutoCommit(false);
                String query = buildQueryForInsert(encuesta);
                st.executeUpdate(query);
                conn.commit();
                
            }catch(Exception e) {
                if(conn != null) {
                    try {
                        conn.rollback();
                    } catch (SQLException ex) {
                        Logger.getLogger(ConectorBaseDatos.class.getName()).log(Level.SEVERE, null, ex);
                    }
                }
                e.printStackTrace();
            }finally {
                if(conn != null) {
                    try {
                        conn.close();
                    } catch (SQLException ex) {
                        Logger.getLogger(ConectorBaseDatos.class.getName()).log(Level.SEVERE, null, ex);
                    }
                }
            }
        }

    private String buildQueryForInsert(Encuesta encuesta) {
                String query = " insert into " + tabla +" (ID_SURVEY";
                for(PreguntaRespuesta pareja : encuesta.getAnswers()) {
                    if(!ignorarColumnas.contains(pareja.getQuestion())){
                        query += "," + pareja.getQuestion();
                    }
                }
                query += ")values(\"" + encuesta.getSurvey_id() + "\"";
                for(PreguntaRespuesta pareja : encuesta.getAnswers()) {
                    if(!ignorarColumnas.contains(pareja.getQuestion())){
                        query += ",\"" + pareja.getAnswer() + "\"";
                    }
                }
                query += ")";
                return query;
    }
        
}
